package es.ats.codington.festival.daos;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import es.ats.codington.festival.domain.Event;
import es.ats.codington.festival.domain.LargeBusiness;
import es.ats.codington.festival.domain.Museum;
import es.ats.codington.festival.domain.Park;
import es.ats.codington.festival.domain.Place.Zone;
import es.ats.codington.festival.domain.Stadium;
import es.ats.codington.festival.domain.Theater;
import es.ats.codington.festival.domain.TouristAttraction;
import es.ats.codington.festival.domain.TraditionalMarket;
import es.ats.codington.festival.domain.Visitor;
import es.ats.codington.festival.domain.Zoo;
import es.ats.codington.festival.interfaces.daos.IEventDAO;
//import es.ats.codington.festival.utils.JSonQuery;

/**
 * 
 * EventDAOImpl class
 *
 */
public class EventDAOImpl implements IEventDAO {


	// -------------------------------------------
	
	private Connection connection = null;
	private PreparedStatement statement = null;
	private ResultSet resultSet = null;
	//JSonQuery json = new JSonQuery();
	
	/**
	 * Constructor
	 * 
	 * @param connection
	 */
	public EventDAOImpl(Connection connection) {
		this.connection = connection;
	}
		
	// --------------------------Methods ------------------------------
	
	/**
	 * ShowEvents
	 * 
	 * @return
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public ArrayList<Event> showEvents() throws ClassNotFoundException, SQLException {
		ArrayList<Event> eventList = new ArrayList<Event>();
		try{
		//statement = connection.prepareStatement(json.getShowEvents());
		statement = connection.prepareStatement("SELECT * From event");	
		resultSet = statement.executeQuery();
		
		while (resultSet.next()) {
			Event event = new Event();

			event.setIdEvent(resultSet.getInt("idEvent"));
			event.setEventName(resultSet.getString("eventName"));
			event.setDescription(resultSet.getString("description"));
			event.setDuration(resultSet.getInt("duration"));
			event.setEventType(resultSet.getString("eventType"));
			event.setSeatsAvailable(resultSet.getInt("seatsAvailable"));
			event.setSeatsTotal(resultSet.getInt("seatsTotal"));
			event.setPlace(resultSet.getString("place"));

			eventList.add(event);	
		}
		} catch(SQLException ex) {
			System.out.println(ex.getMessage());
		}
		resultSet.close();
		return eventList;
	}
	
	/**
	 * RegisterNewEvent
	 * 
	 * @param event
	 * @return
	 */
	public int registerNewEvent(Event event)throws ClassNotFoundException, SQLException {
		int affectedRows = 0;	
		try {
			//statement = connection.prepareStatement(json.getRegisterNewEvent());
			statement = connection.prepareStatement("INSERT into event(eventName,description,duration,eventType,seatsAvailable,seatsTotal,place) values (?,?,?,?,?,?,?)");
			statement.setString(1, event.getEventName());
			statement.setString(2, event.getDescription());
			statement.setInt(3, event.getDuration());
			statement.setString(4, event.getEventType());
			statement.setInt(5, event.getSeatsAvailable());
			statement.setInt(6, event.getSeatsTotal());
			statement.setString(7, event.getPlace());
			
			affectedRows = statement.executeUpdate();

		} catch(SQLException ex) {
			System.out.println(ex.getMessage());
		}
		return affectedRows;
		}
	
	/**
	 * DeleteEvent
	 * 
	 * @param idEvent
	 * @return
	 */
	public int deleteEvent(int idEvent) throws ClassNotFoundException, SQLException {
		int affectedRows = 0;			 			
		try {
			//statement = connection.prepareStatement(json.getDeleteEvent());
			statement = connection.prepareStatement("DELETE from event where idEvent=?");
		    statement.setInt(1, idEvent);
		    
		    affectedRows = statement.executeUpdate();
		    
		} catch(SQLException ex) {
			System.out.println(ex.getMessage());
		}
		return affectedRows;
	}
	
	/**
	 * UpdateEvent
	 * 
	 * @param event
	 * @return
	 */
	public int updateEvent(Event event) throws ClassNotFoundException, SQLException {
		int affectedRows = 0;	
		try {
			//statement = connection.prepareStatement(json.getUpdateEvent());
			statement = connection.prepareStatement("UPDATE event set eventName=?, description=?, duration=?,eventType=?,seatsAvailable=?,seatsTotal=?, place=? WHERE idEvent=?");
			statement.setString(1, event.getEventName());
			statement.setString(2, event.getDescription());
			statement.setInt(3, event.getDuration());
			statement.setString(4, event.getEventType());
			statement.setInt(5, event.getSeatsAvailable());
			statement.setInt(6, event.getSeatsTotal());
			statement.setString(7, event.getPlace());
			statement.setInt(8, event.getIdEvent());
			
			affectedRows = statement.executeUpdate();

		} catch(SQLException ex) {
			System.out.println(ex.getMessage());
		}
		return affectedRows;
		}
	
	/**
	 * CheckEventDao
	 * 
	 * @param event
	 * @return
	 * @throws SQLException
	 */
	public boolean checkEventDAO(Event event) throws SQLException{
		int row =0;
		try{	
			//statement = connection.prepareStatement(json.getCheckEvent());
			statement = connection.prepareStatement("SELECT * FROM event WHERE eventName=?");
			statement.setString(1, event.getEventName());
			
			resultSet = statement.executeQuery();
			
			resultSet.last();
			row = resultSet.getRow();
			resultSet.beforeFirst();
						
		}catch(Exception eee){
			eee.getMessage();
		}
			
		return row==1;
	}
	
	/**
	 * GetIdEvent
	 * 
	 * @param event
	 * @return
	 * @throws SQLException
	 */
	public int getIdEventDAO(Event event) throws SQLException{
		int idEvent=0;
		try{	
			//statement = connection.prepareStatement(json.getGetIdEvent());
			statement = connection.prepareStatement("SELECT idEvent FROM event WHERE eventName=?");
			statement.setString(1, event.getEventName());
			resultSet = statement.executeQuery();
			
			ResultSet keys= statement.executeQuery();
			keys.next();
			idEvent =keys.getInt(1);
			
		}catch(Exception eee){
			eee.getMessage();
		}
		return idEvent;
	}
	
	
	
	public Event getEventDAO(Event e) throws SQLException{

		try{		    	
			
		//	statement = connection.prepareStatement(json.getGetVisitor());
			statement = connection.prepareStatement("SELECT * FROM event WHERE idEvent=?");
			
			statement.setInt(1, e.getIdEvent());
			
			
			ResultSet resultset = statement.executeQuery();
			

			
			resultset.next();

			e.setEventName(resultset.getString("eventName"));

			e.setDescription(resultset.getString("description"));

			e.setDuration(resultset.getInt("duration"));
	
			e.setEventType(resultset.getString("eventType"));
		
			e.setSeatsAvailable(resultset.getInt("seatsAvailable"));
		
			e.setSeatsTotal(resultset.getInt("seatsTotal"));
			
			e.setPlace(resultset.getString("place"));
			
					
	
		
		} catch (Exception ee) {
			System.out.println("getVisitorDAO: "+ ee.getMessage());
			

		} 
		return e;	
		}
	
	
	public ArrayList<Event> showEventsByPlaceDAO(Event event) throws ClassNotFoundException, SQLException {
		ArrayList<Event> eventList = new ArrayList<Event>();
		try{
		statement = connection.prepareStatement("SELECT * from event where place=?");
		statement.setString(1, event.getPlace());
		resultSet = statement.executeQuery();
		
		while (resultSet.next()) {
			Event event1 = new Event();

			event1.setIdEvent(resultSet.getInt("idEvent"));
			event1.setEventName(resultSet.getString("eventName"));
			event1.setDescription(resultSet.getString("description"));
			event1.setDuration(resultSet.getInt("duration"));
			event1.setEventType(resultSet.getString("eventType"));
			event1.setSeatsAvailable(resultSet.getInt("seatsAvailable"));
			event1.setSeatsTotal(resultSet.getInt("seatsTotal"));
			event1.setPlace(resultSet.getString("place"));

			eventList.add(event1);	
		}
		} catch(SQLException ex) {
			System.out.println(ex.getMessage());
		}
		resultSet.close();
		return eventList;
	}
	
	public Stadium showStadiumDAO(Stadium stadium) throws ClassNotFoundException, SQLException {
		
		try{
		statement = connection.prepareStatement("SELECT * FROM place,indoorplace, stadium WHERE place.idPlace=? AND indoorplace.idPlace=? AND stadium.idPlace=?;");
		statement.setInt(1, 1);
		statement.setInt(2, 1);
		statement.setInt(3, 1);
		resultSet = statement.executeQuery();
		
		resultSet.next();

			stadium.setIdPlace(resultSet.getInt("idPlace"));
			stadium.setPlaceName(resultSet.getString("placeName"));
			stadium.setZone(Zone.NORTH);
			stadium.setCapacity(resultSet.getInt("capacity"));
			stadium.setStadiumName(resultSet.getString("stadiumName"));
	
		
		} catch(SQLException ex) {
			ex.getMessage();
		}
		resultSet.close();
		return stadium;
	}
	
public Theater showTheaterDAO(Theater theater) throws ClassNotFoundException, SQLException {
		
		try{
		statement = connection.prepareStatement("SELECT * FROM place,indoorplace, theater WHERE place.idPlace=? AND indoorplace.idPlace=? AND theater.idPlace=?;");
		statement.setInt(1, 2);
		statement.setInt(2, 2);
		statement.setInt(3, 2);
		resultSet = statement.executeQuery();
		
		resultSet.next();

		theater.setIdPlace(resultSet.getInt("idPlace"));
		theater.setPlaceName(resultSet.getString("placeName"));
		theater.setZone(Zone.NORTH);
		theater.setCapacity(resultSet.getInt("capacity"));
		theater.setTheaterName(resultSet.getString("theaterName"));
	
		
		} catch(SQLException ex) {
			ex.getMessage();
		}
		resultSet.close();
		return theater;
	}
public Museum showMuseumDAO(Museum museum) throws ClassNotFoundException, SQLException {
	
	try{
	statement = connection.prepareStatement("SELECT * FROM place,indoorplace, museum WHERE place.idPlace=? AND indoorplace.idPlace=? AND museum.idPlace=?;");
	statement.setInt(1, 3);
	statement.setInt(2, 3);
	statement.setInt(3, 3);
	resultSet = statement.executeQuery();
	
	resultSet.next();

	museum.setIdPlace(resultSet.getInt("idPlace"));
	museum.setPlaceName(resultSet.getString("placeName"));
	museum.setZone(Zone.NORTH);
	museum.setCapacity(resultSet.getInt("capacity"));
	museum.setMuseumName(resultSet.getString("museumName"));

	
	} catch(SQLException ex) {
		ex.getMessage();
	}
	resultSet.close();
	return museum;
}
public LargeBusiness showLargeBusinessDAO(LargeBusiness largeBusiness) throws ClassNotFoundException, SQLException {
	
	try{
	statement = connection.prepareStatement("SELECT * FROM place,indoorplace, largeBusiness WHERE place.idPlace=? AND indoorplace.idPlace=? AND largeBusiness.idPlace=?;");
	statement.setInt(1, 4);
	statement.setInt(2, 4);
	statement.setInt(3, 4);
	resultSet = statement.executeQuery();
	
	resultSet.next();

	largeBusiness.setIdPlace(resultSet.getInt("idPlace"));
	largeBusiness.setPlaceName(resultSet.getString("placeName"));
	largeBusiness.setZone(Zone.NORTH);
	largeBusiness.setCapacity(resultSet.getInt("capacity"));
	largeBusiness.setLargeBusinessName(resultSet.getString("largeBusinessName"));

	
	} catch(SQLException ex) {
		ex.getMessage();
	}
	resultSet.close();
	return largeBusiness;
}

public Zoo showZooDAO(Zoo zoo) throws ClassNotFoundException, SQLException {
	
	try{
	statement = connection.prepareStatement("SELECT * FROM place,outdoorplace, zoo WHERE place.idPlace=? AND outdoorplace.idPlace=? AND zoo.idPlace=?;");
	statement.setInt(1, 5);
	statement.setInt(2, 5);
	statement.setInt(3, 5);
	resultSet = statement.executeQuery();
	
	resultSet.next();

	zoo.setIdPlace(resultSet.getInt("idPlace"));
	zoo.setPlaceName(resultSet.getString("placeName"));
	zoo.setZone(Zone.SOUTH);
	zoo.setZooName(resultSet.getString("ZooName"));

	
	} catch(SQLException ex) {
		ex.getMessage();
	}
	resultSet.close();
	return zoo;
}
public Park showParkDAO(Park park) throws ClassNotFoundException, SQLException {
	
	try{
	statement = connection.prepareStatement("SELECT * FROM place,outdoorplace, park WHERE place.idPlace=? AND outdoorplace.idPlace=? AND park.idPlace=?;");
	statement.setInt(1, 6);
	statement.setInt(2, 6);
	statement.setInt(3, 6);
	resultSet = statement.executeQuery();
	
	resultSet.next();

	park.setIdPlace(resultSet.getInt("idPlace"));
	park.setPlaceName(resultSet.getString("placeName"));
	park.setZone(Zone.SOUTH);
	park.setparkName(resultSet.getString("ParkName"));

	
	} catch(SQLException ex) {
		ex.getMessage();
	}
	resultSet.close();
	return park;
}
public TraditionalMarket showTraditionalMarketDAO(TraditionalMarket traditionalMarket) throws ClassNotFoundException, SQLException {
	
	try{
	statement = connection.prepareStatement("SELECT * FROM place,outdoorplace, traditionalmarket WHERE place.idPlace=? AND outdoorplace.idPlace=? AND traditionalmarket.idPlace=?;");
	statement.setInt(1, 7);
	statement.setInt(2, 7);
	statement.setInt(3, 7);
	resultSet = statement.executeQuery();
	
	resultSet.next();

	traditionalMarket.setIdPlace(resultSet.getInt("idPlace"));
	traditionalMarket.setPlaceName(resultSet.getString("placeName"));
	traditionalMarket.setZone(Zone.SOUTH);
	traditionalMarket.setMarketName(resultSet.getString("marketName"));

	
	} catch(SQLException ex) {
		ex.getMessage();
	}
	resultSet.close();
	return traditionalMarket;
}
public TouristAttraction showTouristAttractionDAO(TouristAttraction touristAttraction) throws ClassNotFoundException, SQLException {
	
	try{
	statement = connection.prepareStatement("SELECT * FROM place,outdoorplace, touristattraction WHERE place.idPlace=? AND outdoorplace.idPlace=? AND touristattraction.idPlace=?;");
	statement.setInt(1, 8);
	statement.setInt(2, 8);
	statement.setInt(3, 8);
	resultSet = statement.executeQuery();
	
	resultSet.next();

	touristAttraction.setIdPlace(resultSet.getInt("idPlace"));
	touristAttraction.setPlaceName(resultSet.getString("placeName"));
	touristAttraction.setZone(Zone.SOUTH);
	touristAttraction.setAttractionName(resultSet.getString("AttractionName"));

	
	} catch(SQLException ex) {
		ex.getMessage();
	}
	resultSet.close();
	return touristAttraction;
}





	
}
